"""
Case Type   : Ubtree
Case Name   : 建表插入数据，创建ubtree索引指定pcr_逻辑解码
Create At   : 2025-10-11
Owner       : sungang14
Description :
    1、设置wal_level = logical
    2、创建逻辑复制槽
    3、建表;
    4、建索引时指定PCR
    5、插入数据
    6、对该列存表进行逻辑解码
    7、删除新建逻辑复制槽与表
Expect      :
    1、设置成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.node_pri = Node(node='PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0106'
        self.prepare_data_00 = f"drop table if exists {self.table_name} cascade;"
        self.prepare_data_001 = f"SELECT * FROM pg_create_logical_replication_slot('ubtree_case0106', 'mppdb_decoding');"
        self.prepare_data_01 = f"""create table {self.table_name} (
                                        id int primary key,
                                        dept varchar(20),
                                        name varchar(50),
                                        salary numeric(10,2)
                                    ) with (storage_type=ustore);"""
        self.prepare_data_02 = f"create index i_ubtree_0101 on {self.table_name} using ubtree(id) with (index_type=pcr);"
        self.prepare_data_03 = f"insert into {self.table_name} select generate_series(1,1000000),'dep'||(random()*5)::int, 'emp_'||lpad((random()*999999)::int::text,5,'0'),(random()*100000+50000)::numeric(10,2);"
        self.prepare_data_04 = f"SELECT * FROM pg_logical_slot_peek_changes('ubtree_case0106', NULL, 4096);"
        self.prepare_data_05 = f"SELECT * FROM pg_drop_replication_slot('ubtree_case0106');"
        self.set_wal_level = "wal_level = logical"
        self.show_wal_level = 'show wal_level;'
        self.show_res = self.comsh.execut_db_sql(self.show_wal_level)
        logger.info(self.show_res)

    def test_slowsql(self):
        text = '--step1:设置wal_level = logical;expect:设置成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set', self.constant.GSGUC_SUCCESS_MSG,
                                       self.set_wal_level)
        logger.info(msg)
        logger.info('---------------------重启数据库--------------------')
        self.comsh.restart_db_cluster()
        status = self.comsh.get_db_cluster_status()
        self.assertTrue("Normal" in status or 'Degraded' in status)
        msg1 = self.comsh.execut_db_sql('show wal_level;')
        logger.info(msg1)
        self.new_value = msg1.splitlines()[-2].strip()
        self.assertIn('logical', self.new_value)

        text = '--step2:创建逻辑复制槽;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_001)
        logger.info(data_cmd)
        self.assertIn(f"1 row", data_cmd, '执行失败' + text)

        text = '--step3:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step4:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step5:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '-----step6:对该列存表进行逻辑解码;expect:成功-----'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(f"1000006 rows", data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step7:清理环境;expect:成功--'
        logger.info(text)
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(f"1 row", data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
